using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace DataAccessLayer
{
    public class ReviewGateway
    {

        private SqlConnection connection;

        private SqlCommand command;
        private SqlDataAdapter adapter;
        private SqlCommandBuilder builder;

        private SqlCommand reviewRecordingCommand;
        private SqlDataAdapter reviewRecordingAdapter;
        private SqlCommandBuilder reviewRecordingBuilder;

        public ReviewGateway(SqlConnection connection)
        {
            this.connection = connection;

            command = new SqlCommand("SELECT * FROM Review WHERE id = @id", connection);
            command.Parameters.Add("@id", SqlDbType.BigInt);
            adapter = new SqlDataAdapter(command);
            builder = new SqlCommandBuilder(adapter);

            reviewRecordingCommand = new SqlCommand("SELECT * FROM Review WHERE recordingid = @id", connection);
            reviewRecordingCommand.Parameters.Add("@id", SqlDbType.BigInt);
            reviewRecordingAdapter = new SqlDataAdapter(reviewRecordingCommand);
            reviewRecordingBuilder = new SqlCommandBuilder(reviewRecordingAdapter);

        }

        public long Insert(int rating, string review, RecordingDataSet recordingDataSet)
        {
            RecordingDataSet.ReviewRow newRow = recordingDataSet.Review.NewReviewRow();
            newRow.id = IdGenerator.GetNextId(recordingDataSet.Review.TableName, connection);
            newRow.rating = rating;
            newRow.review = review;
            recordingDataSet.Review.AddReviewRow(newRow);
            Update(recordingDataSet);
            return newRow.id;
        }

        public RecordingDataSet.ReviewRow FindById(long id, RecordingDataSet recordingDataSet)
        {
            command.Parameters["@id"].Value = id;
            adapter.Fill(recordingDataSet, recordingDataSet.Review.TableName);

            DataRow[] rows = recordingDataSet.Review.Select(String.Format("id={0}", id));

            if (rows.Length < 1) return null;

            return (RecordingDataSet.ReviewRow)rows[0];
        }

        public RecordingDataSet.ReviewRow[] FindByRecordingId(long recordingId, RecordingDataSet recordingDataSet)
        {
            reviewRecordingCommand.Parameters["@id"].Value = recordingId;
            reviewRecordingAdapter.Fill(recordingDataSet, recordingDataSet.Review.TableName);

            DataRow[] rows = recordingDataSet.Review.Select(String.Format("id={0}", recordingId));

            return (RecordingDataSet.ReviewRow[])rows;
        }

        public void Delete(long deletedId, RecordingDataSet recordingDataSet)
        {
            RecordingDataSet.ReviewRow row = FindById(deletedId, recordingDataSet);
            row.Delete();
            Update(recordingDataSet);
        }

        public void Update(RecordingDataSet recordingDataSet)
        {
            adapter.Update(recordingDataSet, recordingDataSet.Review.TableName);
        }
    }
}
